﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using BookClubIt.DTO;
using System.Data.SqlClient;
using BookClubIt.Data.Commands;
using System.Data;

namespace BookClubIt.Data
{
    public class BookData
    {
        public Book GetBookById(int id)
        {
            Book book = new Book();
            BciDatabase db = new BciDatabase("Bookclub");
            using (SqlDataReader reader = db.ExecuteReader(CommandType.Text, string.Format("SELECT * from book where id = {0}", id)))
            {
                if (reader.Read())
                {
                    book = BuildBook(reader);
                }
            }
            return book;
        }

        public Book GetBookByASIN(string asin)
        {
            Book book = new Book();
            BciDatabase db = new BciDatabase("Bookclub");
            using (SqlDataReader reader = db.ExecuteReader(CommandType.Text, string.Format("SELECT * from book where asin = '{0}'", asin)))
            {
                if (reader.Read())
                {
                    book = BuildBook(reader);
                }
            }
            return book;
        }

        public Book GetCurrentBook(int bid)
        {
            Book book = new Book();
            BciDatabase db = new BciDatabase("Bookclub");
            using (SqlDataReader reader = db.ExecuteReader(CommandType.Text, string.Format(@"SELECT b.Id,b.Title,b.ASIN,b.EAN,b.ISBN,b.Amazon_URL,b.Large_Image_URL,b.Medium_Image_URL,b.Small_Image_URL,b.Author,b.Binding,b.Manufacturer,b.Pages,b.Published_Date,b.Publisher,b.Reading_Level,b.Editorial from Book_In_Club bic  
                LEFT JOIN Book b ON bic.Book_Id = b.Id 
                where bic.Book_Club_Id = {0} AND Is_Current = 1", bid)))
            {
                if (reader.Read())
                {
                    book = BuildBook(reader);
                }
            }
            return book;
        }

        public bool haveBookInDb(string asin)
        {
            BciDatabase db = new BciDatabase("Bookclub");
            using (SqlDataReader reader = db.ExecuteReader(CommandType.Text, string.Format("SELECT * FROM Book WHERE ASIN = '{0}'", asin)))
            {
                if (reader.HasRows)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
        }
        public Books GetBooksForClub(int bid)
        {
            Books books = new Books();
            BciDatabase db = new BciDatabase("Bookclub");
            using (SqlDataReader reader = db.ExecuteReader(CommandType.Text, string.Format(@"SELECT b.* 
                from book b
                inner join book_in_club bic on bic.book_id = b.id
                where bic.Book_club_id = {0}", bid)))
            {
                while (reader.Read())
                {
                    books.Add(BuildBook(reader));
                }
            }
            return books;
        }

       
        private Book BuildBook(SqlDataReader reader)
        {
            return new Book
            {
                Id = ConvertDBNull.To<int>(reader["Id"], 0),
                Title = ConvertDBNull.To<string>(reader["Title"], string.Empty),
                ASIN = ConvertDBNull.To<string>(reader["ASIN"], string.Empty),
                EAN = ConvertDBNull.To<string>(reader["EAN"], string.Empty),
                ISBN = ConvertDBNull.To<string>(reader["ISBN"], string.Empty),
                AmazonURL = ConvertDBNull.To<string>(reader["Amazon_URL"], string.Empty),
                LargeImageURL = ConvertDBNull.To<string>(reader["Large_Image_URL"], string.Empty),
                MediumImageURL = ConvertDBNull.To<string>(reader["Medium_Image_URL"], string.Empty),
                SmallImageURL = ConvertDBNull.To<string>(reader["Small_Image_URL"], string.Empty),
                Author = ConvertDBNull.To<string>(reader["Author"], string.Empty),
                Binding = ConvertDBNull.To<string>(reader["Binding"], string.Empty),
                Manufacturer = ConvertDBNull.To<string>(reader["Manufacturer"], string.Empty),
                Pages = ConvertDBNull.To<string>(reader["Pages"], string.Empty),
                PublishedDate = ConvertDBNull.To<DateTime>(reader["Published_Date"], DateTime.MinValue),
                Publisher = ConvertDBNull.To<string>(reader["Publisher"], string.Empty),
                ReadingLevel = ConvertDBNull.To<string>(reader["Reading_Level"], string.Empty),
                Editorial = ConvertDBNull.To<string>(reader["Editorial"], string.Empty),
            };
        }

        public Book SaveBook(DTO.Book book)
        {
            BciDatabase db = new BciDatabase("Bookclub");
            SqlCommand command = BookCommandList.SaveBook;
            command.Parameters["@Id"].Value = book.Id;
            command.Parameters["@Title"].Value = book.Title;
            command.Parameters["@ASIN"].Value = book.ASIN;
            command.Parameters["@EAN"].Value = book.EAN;
            command.Parameters["@ISBN"].Value = book.ISBN;
            command.Parameters["@AmazonURL"].Value = book.AmazonURL;
            command.Parameters["@LargeImageURL"].Value = book.LargeImageURL;
            command.Parameters["@MediumImageURL"].Value = book.MediumImageURL;
            command.Parameters["@SmallImageURL"].Value = book.SmallImageURL;
            command.Parameters["@Author"].Value = book.Author;
            command.Parameters["@Binding"].Value = book.Binding;
            command.Parameters["@Manufacturer"].Value = book.Manufacturer;
            command.Parameters["@Pages"].Value = book.Pages;
            command.Parameters["@PublishedDate"].Value = book.PublishedDate;
            command.Parameters["@Publisher"].Value = book.Publisher;
            command.Parameters["@ReadingLevel"].Value = book.ReadingLevel;
            command.Parameters["@Editorial"].Value = book.Editorial;
            db.ExecuteNonQuery(command);

            book.Id = Convert.ToInt32(command.Parameters["@Id"].Value);

            return book;
        }

        
 
    }
}
